Namespace persistent
    Public Class billcycleUpdate

        Public Shared Function getGridData(ByVal customerID As String) As DataTable
            Dim SQL As String

            SQL = "select cus.customer_id c1,cus.billcycle BID, cus.billcycle||' '||b.description BCYCLE,m.des CUSTMCODE "
            SQL = SQL & " from customer_all cus, billcycles b,mputmtab m where cus.billcycle=b.billcycle "
            SQL = SQL & "  and cus.tmcode=m.tmcode and m.vscode=0 and cus.customer_id= " & customerID & "  "

            Return myDBConn.myORATable(SQL)
        End Function

        Public Shared Function getCoGridData(ByVal customerID As String) As DataTable
            Dim SQL As String

            SQL = " SELECT co.co_id COID,dn.dn_num MSISDN,m.des tmcode,ch.ch_validfrom ACT_DATE,CUR.ddate DEACT_DATE  "
            SQL = SQL & " FROM "
            SQL = SQL & " contr_services_cap cap,eu_bill.view_directory_number dn,contract_history ch,contract_all co,MPUTMTAB M,  "
            SQL = SQL & " (SELECT curr.co_id,curr.ch_validfrom ddate FROM contract_history curr WHERE ch_status='d')cur  "
            SQL = SQL & " WHERE cap.co_id=co.co_id  "
            SQL = SQL & " AND cap.dn_id=dn.dn_id  "
            SQL = SQL & " AND co.co_id=ch.co_id"
            SQL = SQL & " AND ch.co_id=cur.co_id(+)"
            SQL = SQL & " AND CO.TMCODE=M.TMCODE"
            SQL = SQL & " AND CAP.SEQNO=(SELECT MAX(CAP1.SEQNO) FROM CONTR_SERVICES_CAP CAP1 WHERE CAP.CO_ID=CAP1.CO_ID)"
            SQL = SQL & " AND CH.CH_SEQNO=2"
            SQL = SQL & " AND cap.co_id>1"
            SQL = SQL & " AND dn.dn_id>1"
            SQL = SQL & " AND m.vscode=0"
            SQL = SQL & " AND Co.customer_ID='" & customerID & "'"

            Return myDBConn.myORATable(SQL)

        End Function

        Public Shared Function updateBillcycle(ByVal customerID As String, ByVal billcycle As String) As Long
            Dim SQL As String

            SQL = "update customer_all set billcycle ='" & billcycle & "' where customer_id =" & customerID & ""

            Return myDBConn.ORAExecute(SQL)

        End Function

        Public Shared Function getOldBillcycle(ByVal customerID As String) As DataTable
            Dim SQL As String

            SQL = "select cus.billcycle||' '||b.description billcycle "
            SQL = SQL & " from customer_all cus,billcycles b where cus.billcycle=b.billcycle and cus.customer_id=" & customerID & ""

            Return myDBConn.myORATable(SQL)
        End Function

        Public Shared Function insertBillcycleLog(ByVal customerID As String, ByVal oldBillCycle As String, ByVal newBillCycle As String, ByVal user As String) As Long
            Dim SQL As String

            SQL = "insert into bcupdlog (custid,obillcycle,nbillcycle,usr) values(" & customerID & ",'" & oldBillCycle & "','" & newBillCycle & "','" & user & "');"

            Return myDBConn.Execute(SQL)

        End Function

    End Class
End Namespace
